﻿using System;
using System.Collections.Generic;
using System.Linq.Expressions;
using System.Reflection;

namespace NPoco
{
    public static class DataBaseExtensions
    {

        public static string getKeyname<T>(Expression<Func<T, object>> property)
        {
            string keyname = property.Body.ToString();
            keyname = keyname.Substring(keyname.LastIndexOf(".") + 1);
            keyname = keyname.Replace(")", "").Replace("}", "");
            return GetCloumnName(typeof(T).GetProperty(keyname));
        }

        private static string GetCloumnName(PropertyInfo t)
        {
            var attrs = t.GetCustomAttributes(true);
            foreach (var obj in attrs)
            {
                var attr = obj as ColumnAttribute;
                if (attr != null)
                {
                    return attr.Name;
                }
            }
            return string.Empty;
        }

        /// <summary>
        /// 根据指定列的值查找记录(非唯一索引不建议,列未建索引不建议)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="db"></param>
        /// <param name="property"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public static T SingleOrDefault<T>(this IDatabase db, Expression<Func<T, object>> property, object key)
        {
            try
            {
                return db.SingleOrDefault<T>(new Sql("where " + getKeyname(property) + "=@0", key));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据指定列的值分页检索
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="db"></param>
        /// <param name="page"></param>
        /// <param name="itemsPerPage"></param>
        /// <param name="property"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public static Page<T> Page<T>(this IDatabase db, long page, long itemsPerPage, Expression<Func<T, object>> property, object key)
        {
            try
            {
                return db.Page<T>(page, itemsPerPage, new Sql("where " + getKeyname(property) + "=@0", key));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据指定列查询List集合(大表查询不建议,列未建索引不建议)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="db"></param>
        /// <param name="property"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public static List<T> Fetch<T>(this IDatabase db, Expression<Func<T, object>> property, object key)
        {
            try
            {
                return db.Fetch<T>(new Sql("where " + getKeyname(property) + "=@0", key));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

    /// <summary>
    /// Npoco SQL动态生成器
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class ParametersList<T>
    {

        class Parameters : ColumnKey
        {
            public object Value { get; set; }
        }

        class ColumnKey
        {
            public Expression<Func<T, object>> Key { get; set; }
            public EnumOperator Operator { get; set; }
        }

        private List<Parameters> wherelist = new List<Parameters>();
        private List<ColumnKey> orderlist = new List<ColumnKey>();

        /// <summary>
        /// 增加条件
        /// </summary>
        /// <param name="_Key"></param>
        /// <param name="_Operator"></param>
        /// <param name="_Value"></param>
        public void Add(Expression<Func<T, object>> _Key, EnumOperator _Operator, object _Value)
        {
            wherelist.Add(new Parameters()
            {
                Key = _Key,
                Operator = _Operator,
                Value = _Value,
            });
        }

        /// <summary>
        /// 增加等于条件
        /// </summary>
        /// <param name="_Key"></param>
        /// <param name="_Value"></param>
        public void AddEquals(Expression<Func<T, object>> _Key, object _Value)
        {
            Add(_Key, EnumOperator.Equals, _Value);
        }

        /// <summary>
        /// 增加Like 后%条件
        /// </summary>
        /// <param name="_Key"></param>
        /// <param name="_Value"></param>
        public void AddLike(Expression<Func<T, object>> _Key, object _Value)
        {
            Add(_Key, EnumOperator.Like, _Value);
        }

        /// <summary>
        /// 增加Like %%条件
        /// </summary>
        /// <param name="_Key"></param>
        /// <param name="_Value"></param>
        public void AddLikeAll(Expression<Func<T, object>> _Key, object _Value)
        {
            Add(_Key, EnumOperator.LikeAll, _Value);
        }

        /// <summary>
        /// 增加不等于条件
        /// </summary>
        /// <param name="_Key"></param>
        /// <param name="_Value"></param>
        public void AddNotEquals(Expression<Func<T, object>> _Key, object _Value)
        {
            Add(_Key, EnumOperator.NotEquals, _Value);
        }

        /// <summary>
        /// 添加小于条件
        /// </summary>
        /// <param name="_Key"></param>
        /// <param name="_Value"></param>
        public void AddLess(Expression<Func<T, object>> _Key, object _Value)
        {
            Add(_Key, EnumOperator.Less, _Value);
        }

        /// <summary>
        /// 添加小于等于条件
        /// </summary>
        /// <param name="_Key"></param>
        /// <param name="_Value"></param>
        public void AddLessOrEquals(Expression<Func<T, object>> _Key, object _Value)
        {
            Add(_Key, EnumOperator.LessOrEquals, _Value);
        }

        /// <summary>
        /// 添加大于条件
        /// </summary>
        /// <param name="_Key"></param>
        /// <param name="_Value"></param>
        public void AddGreater(Expression<Func<T, object>> _Key, object _Value)
        {
            Add(_Key, EnumOperator.Greater, _Value);
        }

        /// <summary>
        /// 添加大于等于条件
        /// </summary>
        /// <param name="_Key"></param>
        /// <param name="_Value"></param>
        public void AddGreaterOrEquals(Expression<Func<T, object>> _Key, object _Value)
        {
            Add(_Key, EnumOperator.GreaterOrEquals, _Value);
        }

        /// <summary>
        /// 列为空
        /// </summary>
        /// <param name="_Key"></param>
        public void IsNull(Expression<Func<T, object>> _Key)
        {
            Add(_Key, EnumOperator.IsNull, null);
        }

        /// <summary>
        /// 列不为空
        /// </summary>
        /// <param name="_Key"></param>
        public void IsNotNull(Expression<Func<T, object>> _Key)
        {
            Add(_Key, EnumOperator.IsNotNull, null);
        }

        /// <summary>
        /// 添加排序
        /// </summary>
        /// <param name="_Key"></param>
        public void AddOrderBy(Expression<Func<T, object>> _Key)
        {
            orderlist.Add(new ColumnKey() { Key = _Key, Operator = EnumOperator.OrderBy });
        }

        /// <summary>
        /// 添加倒序排序
        /// </summary>
        /// <param name="_Key"></param>
        public void AddOrderByDesc(Expression<Func<T, object>> _Key)
        {
            orderlist.Add(new ColumnKey() { Key = _Key, Operator = EnumOperator.OrderByDesc });
        }

        /// <summary>
        /// 生成SQL对象
        /// </summary>
        /// <returns></returns>
        public Sql ToSQL()
        {
            var sql = new Sql();

            wherelist.ForEach(p => {
                string columnname = DataBaseExtensions.getKeyname(p.Key);
                switch (p.Operator)
                {
                    case EnumOperator.Like:
                        sql.Append("where " + columnname + " like @0", p.Value + "%");
                        break;
                    case EnumOperator.LikeAll:
                        sql.Append("where " + columnname + " like @0", "%" + p.Value + "%");
                        break;
                    case EnumOperator.NotEquals:
                        sql.Append("where " + columnname + " <> @0", p.Value);
                        break;
                    case EnumOperator.Equals:
                        sql.Append("where " + columnname + "=@0", p.Value);
                        break;
                    case EnumOperator.Less:
                        sql.Append("where " + columnname + "<@0", p.Value);
                        break;
                    case EnumOperator.LessOrEquals:
                        sql.Append("where " + columnname + "<=@0", p.Value);
                        break;
                    case EnumOperator.Greater:
                        sql.Append("where " + columnname + ">@0", p.Value);
                        break;
                    case EnumOperator.GreaterOrEquals:
                        sql.Append("where " + columnname + ">=@0", p.Value);
                        break;
                    case EnumOperator.IsNull:
                        sql.Append("where " + columnname + " is Null ");
                        break;
                    case EnumOperator.IsNotNull:
                        sql.Append("where " + columnname + " is Not Null ");
                        break;
                    default:
                        break;
                }
            });

            orderlist.ForEach(p => {
                string columnname = DataBaseExtensions.getKeyname(p.Key);
                switch (p.Operator)
                {
                    case EnumOperator.OrderBy:
                        sql.Append("order by " + columnname);
                        break;
                    case EnumOperator.OrderByDesc:
                        sql.Append("order by " + columnname + " desc");
                        break;
                    default:
                        break;
                }
            });

            return sql;
        }
    }

    public enum EnumOperator
    {
        /// <summary>
        /// *%
        /// </summary>
        Like,
        /// <summary>
        /// %*%
        /// </summary>
        LikeAll,
        /// <summary>
        /// =
        /// </summary>
        Equals,
        /// <summary>
        /// <>
        /// </summary>
        NotEquals,
        /// <summary>
        /// >=
        /// </summary>
        GreaterOrEquals,
        /// <summary>
        /// >
        /// </summary>
        Greater,
        /// <summary>
        /// <=
        /// </summary>
        LessOrEquals,
        /// <summary>
        /// <
        /// </summary>
        Less,
        /// <summary>
        /// 排序
        /// </summary>
        OrderBy,
        /// <summary>
        /// 倒序
        /// </summary>
        OrderByDesc,
        /// <summary>
        /// 为空
        /// </summary>
        IsNull,
        /// <summary>
        /// 不为空
        /// </summary>
        IsNotNull,
    }
}